<?php
    /**
     * Created by PhpStorm.
     * 版权所有: 2019~2022 [ hhygyl ]
     * Date: 2019/7/1-14:39
     * Link: http://luckyadmin.luckyhhy.cn
     * FileName: PHPExcelService.php
     * Keys: ctrl+alt+L/ctrl+s(代码格式化) ctrl+J(代码提示) ctrl+R(替换)ALT+INSERT(生成代码(如GET,SET方法,构造函数等) , 光标在类中才生效)
     * CTRL+ALT+O (优化导入的类和包 需要配置) SHIFT+F2(高亮错误或警告快速定位错误)
     * CTRL+SHIFT+Z(代码向前) CTRL+SHIFT+/ (块状注释) ctrl+shift+enter(智能完善代码 如if())
     *
     **************************************************************
     *                                                            *
     *   .=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-.       *
     *    |                     ______                     |      *
     *    |                  .-"      "-.                  |      *
     *    |                 /            \                 |      *
     *    |     _          |              |          _     |      *
     *    |    ( \         |,  .-.  .-.  ,|         / )    |      *
     *    |     > "=._     | )(__/  \__)( |     _.=" <     |      *
     *    |    (_/"=._"=._ |/     /\     \| _.="_.="\_)    |      *
     *    |           "=._"(_     ^^     _)"_.="           |      *
     *    |               "=\__|IIIIII|__/="               |      *
     *    |              _.="| \IIIIII/ |"=._              |      *
     *    |    _     _.="_.="\          /"=._"=._     _    |      *
     *    |   ( \_.="_.="     `--------`     "=._"=._/ )   |      *
     *    |    > _.="                            "=._ <    |      *
     *    |   (_/                                    \_)   |      *
     *    |                                                |      *
     *    '-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-='      *
     *                                                            *
     *                    JUST FIND YOUR BUG !                    *
     **************************************************************
     * phpexcel 操作
     */

    /**
     * 调用实例
     *      $export = [];
            foreach ($list as $index=>$item){
            $_info = Db::name('store_order_cart_info')->where('oid',$item['id'])->column('cart_info');
            $goodsName = [];
            foreach ($_info as $k=>$v){
            $v = json_decode($v,true);
            $goodsName[] = implode(
            [$v['productInfo']['store_name'],
            isset($v['productInfo']['attrInfo']) ? '('.$v['productInfo']['attrInfo']['suk'].')' : '',
            "[{$v['cart_num']} * {$v['truePrice']}]"
            ],' ');
            }
            $item['cartInfo'] = $_info;
            $export[] = [
            $item['order_id'],$item['pay_type_name'],
            $item['total_num'],$item['total_price'],$item['total_postage'],$item['pay_price'],$item['refund_price'],
            $item['mark'],$item['remark'],
            [$item['real_name'],$item['user_phone'],$item['user_address']],
            $goodsName,
            [$item['paid'] == 1? '已支付':'未支付','支付时间: '.($item['pay_time'] > 0 ? date('Y/md H:i',$item['pay_time']) : '暂无')]
            ];
            }
            PHPExcelService::setExcelHeader(['订单号','支付方式','商品总数','商品总价','邮费','支付金额','退款金额','用户备注','管理员备注','收货人信息','商品信息','支付状态'])
            ->setExcelTile('订单导出','订单信息'.time(),' 生成时间：'.date('Y-m-d H:i:s',time()))
            ->setExcelContent($export)
            ->ExcelSave();
     */
    namespace service;

    use service\JsonService as Json;

    class PHPExcelService
    {

        //PHPExcel实例化对象
        private static $PHPExcel=null;
        //表头计数
        protected static $count;
        //表头占行数
        protected static $topNumber = 3;
        //表能占据表行的字母对应self::$cellkey
        protected static $cells;
        //表头数据
        protected static $data=[];
        //文件名
        protected static $title='未名名';
        //行宽
        protected static $where=20;
        //行高
        protected static $height=50;
        //表行名
        private static $cellKey = array(
            'A','B','C','D','E','F','G','H','I','J','K','L','M',
            'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
            'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
        );
        //设置style
        private static $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //                PHPExcel_Style_Border里面有很多属性，想要其他的自己去看
                    //                'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    //                'style' => \PHPExcel_Style_Border::BORDER_DOUBLE,//双重的
                    //                'style' => \PHPExcel_Style_Border::BORDER_HAIR,//虚线
                    //                'style' => \PHPExcel_Style_Border::BORDER_MEDIUM,//实粗线
                    //                'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT,//虚粗线
                    //                'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT,//点虚粗线
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                    //'color' => array('argb' => 'FFFF0000'),
                ),
            ),
            'font'=>[
                'bold'=>true
            ],
            'alignment'=>[
                'horizontal'=>\PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical'=>\PHPExcel_Style_Alignment::VERTICAL_CENTER
            ]
        );



        /**
         * @param $data
         * @param $fun
         * @author: hhygyl <hhygyl520@qq.com>
         * @name: initialize
         * @describe:初始化PHPExcel类
         */
        private static function initialize($data,$fun){
            vendor("PHPExcel.PHPExcel.PHPExcel");
            self::$PHPExcel= new \PHPExcel();
            if($fun!==null && is_callable($fun)){
                self::$styleArray=$fun();
            }
            if(!is_array($data)) exit(Json::fail('data 为数组'));
            self::$data=$data;
        }


        /**
         * @param $title
         * @return false|string
         * @author: hhygyl <hhygyl520@qq.com>
         * @name: setUtf8
         * @describe:设置字体格式
         */
        public static function setUtf8($title){
            return iconv('utf-8', 'gb2312', $title);
        }



        /**
         * @param null $list
         * @return $this  也可以为匿名函数 匿名函数参数有 $sheet PHPExcel->getActiveSheet(),self::$topNumber 从第几行设置,$cellkey 行号为数组,self::$cells现在设置的最大行号
         * @author: hhygyl <hhygyl520@qq.com>
         * @name: setExcelContent
         * @describe:execl数据导出
         */
        public function setExcelContent($list=null)
        {
            $sheet=self::$PHPExcel->getActiveSheet();
            foreach(self::$data as $key=>$val){
                $row=self::$cellKey[$key];
                $sheet->getColumnDimension($row)->setWidth(isset($val['w'])?$val['w']:self::$where);
                $sheet->setCellValue($row.self::$topNumber,isset($val['name'])?$val['name']:$val);
            }
            $cellkey=array_slice(self::$cellKey,0,self::$count);
            if($list!==null && is_array($list)){
                foreach ($cellkey as $k=>$v){
                    foreach ($list as $key=>$val){
                        if(isset($val[$k]) && !is_array($val[$k])){
                            $sheet->setCellValue($v.(self::$topNumber+1+$key),$val[$k]);
                        }else if(isset($val[$k]) && is_array($val[$k])){
                            $str='';
                            foreach ($val[$k] as $value){
                                $str.=$value.chr(10);
                            }
                            $sheet->setCellValue($v.(self::$topNumber+1+$key),$str);
                        }
                    }
                }
                $sheet->getDefaultRowDimension()->setRowHeight(self::$height);
                //设置边框
                $sheet->getStyle('A1:'.self::$cells.(count($list)+self::$topNumber))->applyFromArray(self::$styleArray);
                //设置自动换行
                $sheet->getStyle('A4:'.self::$cells.(count($list)+self::$topNumber))->getAlignment()->setWrapText(true);
            }else if($list!==null && is_callable($list)){
                $list($sheet,self::$topNumber,$cellkey,self::$cells)->applyFromArray(self::$styleArray);
            }

            return $this;
        }



        /**
         * @throws \PHPExcel_Reader_Exception
         * @throws \PHPExcel_Writer_Exception
         * @author: hhygyl <hhygyl520@qq.com>
         * @name: ExcelSave
         * @describe:保存表格数据，并下载
         */
        public function ExcelSave(){
            $objWriter=\PHPExcel_IOFactory::createWriter(self::$PHPExcel,'Excel2007');
            $filename=self::$title.'--'.time().'.xlsx';
            ob_end_clean();
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
            $objWriter->save('php://output');
            exit;
        }



        /**
         * @param $data
         * @param null $fun
         * @return PHPExcelService|void
         * @author: hhygyl <hhygyl520@qq.com>
         * @name: setExcelHeader
         * @describe:设置头部信息
         */
        public static function setExcelHeader($data,$fun=null)
        {
            self::initialize($data,$fun);

            if(self::$count=count(self::$data)){
                if(self::$count>count(self::$cellKey)){
                    return Json::fail('表头长度过长');
                }
                self::$cells=self::$cellKey[self::$count-1];
            }else{
                return Json::fail('data 参数二不能为空');
            }
            return new self;
        }



        /**
         * @param string $title  string || array ['title'=>'','name'=>'','info'=>[]]
         * @param string $Name
         * @param array $info
         * @param null $funName   function($style,$A,$A2) 自定义设置头部样式
         * @return $this
         * @author: hhygyl <hhygyl520@qq.com>
         * @name: setExcelTile
         * @describe:设置标题
         */
        public function setExcelTile($title='',$Name='',$info=[],$funName=null){
            //设置参数
            if(is_array($title)){
                if(isset($title['title'])) $title=$title['title'];
                if(isset($title['name'])) $Name=$title['name'];
                if(isset($title['info'])) $info=$title['info'];
            }
            if(empty($title))
                $title=self::$title;
            else
                self::$title=$title;
            if(empty($Name)) $Name=time();
            //设置Excel属性
            self::$PHPExcel ->getProperties()
                ->setCreator("Neo")
                ->setLastModifiedBy("Neo")
                ->setTitle(self::setUtf8($title))
                ->setSubject($Name)
                ->setDescription("")
                ->setKeywords($Name)
                ->setCategory("");
            self::$PHPExcel ->getActiveSheet()->setCellValue('A1', $title);
            //文字居中
            self::$PHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            self::$PHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            self::$PHPExcel->setActiveSheetIndex(0);
            self::$PHPExcel->getActiveSheet()->setTitle($Name);

            self::$PHPExcel->getActiveSheet()->setCellValue('A2',self::setCellInfo($info));
            //合并表头单元格
            self::$PHPExcel->getActiveSheet()->mergeCells('A1:'.self::$cells.'1');
            self::$PHPExcel->getActiveSheet()->mergeCells('A2:'.self::$cells.'2');

            self::$PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
            self::$PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
            //设置表头行高
            if($funName!==null && is_callable($funName)){
                $fontstyle=self::$PHPExcel->getActiveSheet();
                $funName($fontstyle,'A1','A2');
            }else{
                //设置表头字体
                self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
                self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
                self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
                self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
                self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
            }
            self::$PHPExcel->getActiveSheet()->getStyle('A3:'.self::$cells.'3')->getFont()->setBold(true);

            return $this;
        }



        /**
         * @param $info  array (['name'=>'','site'=>'','phone'=>123] || ['我是表名','我是地址','我是手机号码'] ) || string 自定义
         * @return string
         * @author: hhygyl <hhygyl520@qq.com>
         * @name: setCellInfo
         * @describe:设置第二行标题内容
         */
        private static function setCellInfo($info){
            $content=['操作者：','导出日期：'.date('Y-m-d',time()),'地址：','电话：'];
            if(is_array($info) && !empty($info)){
                if(isset($info['name'])){
                    $content[0].=$info['name'];
                }else{
                    $content[0].=isset($info[0])?$info[0]:'';
                }
                if(isset($info['site'])){
                    $content[2].=$info['site'];
                }else{
                    $content[2].=isset($info[1])?$info[1]:'';
                }
                if(isset($info['phone'])){
                    $content[3].=$info['phone'];
                }else{
                    $content[3].=isset($info[2])?$info[2]:'';
                }
                return implode(' ',$content);
            }else if(is_string($info)){
                return empty($info)?implode(' ',$content):$info;
            }
        }


    }